Computerized Investing - Spreadsheet Collection

home *** CD-ROM | disk | FTP | other *** search

/ Computerized Investing - Spreadsheet Collection / Spreadsheet Collection.iso / pc / ibm9 / whatif.doc < prev next >

Wrap

Text File | 1991-07-11 | 19.5 KB | 345 lines

Approaches to Fundamental Valuation What-if?? -- Or How to Examine Changes in Your Assumptions By Fred Shipley, Ph.D. Computerized Investing, May/June 1988 When you look at the valuation models we presented previously (MODELS.DOC), you will note that there are a few variables that are crucial in all of them although there may be a number of means of estimating each one. In the earnings valuation approaches, projected earnings and the price- earnings ratio are the two crucial variables. Moving back one step in that analysis the estimated rate of earnings growth is simply applied to a known value --last year's earnings. Thus the growth rate of earnings and the P/E ratio are the crucial numbers we evaluate. In the dividend valuation approach, the growth of dividends and an investor's required rate of return are the two unknowns. Of course, the required rate of return must be estimated from other more basic information, primarily the risk-free rate of return and the appropriate market risk premium. Finally we can relate the dividend and earnings valuation approaches through the observation that dividends and earnings are related by the payout ratio. This allows us to generate a price-earnings ratio from knowledge of the payout ratio, the required rate of return and the rate of growth. With these concerns in mind, we will explore how changes in these factors can affect the valuation of stock. It is our intention to focus on only a few of these variables; readers interested in exploring other relationships can extend the techniques presented here. We will set up a series of data tables, that is, sensitivity or what-if tables. These will work in sequence from the required rate of return and growth rate to price-earnings ratios. Then from the price-earnings ratios we will look at the sensitivity of valuation estimates. Creating a Data Table A data table is simply 1-2-3's way of constructing the values necessary to perform what-if analysis. We can set up either a one-way table or a two- way table. A one-way data table has one independent variable that is changed, but there may be two formulas dependent on that variable. A two- way data table, on the other hand, has two independent variables whose values are changed, but there can be only one formula incorporating them. Using this feature of 1-2-3 simplifies the what-if testing of our results. To see how this process works, we will examine how the joint effects of changes in the rate of inflation and the market risk premium affect a stock's required rate of return. Remember that an investor's required rate of return is: r = RRf + BETA(RM - RRf) (1) and RRf = Rr + CPI (2) where RRf is the anticipated risk-free rate of return, such as short-term U.S. government securities, RM is the anticipated return on the market as a whole, (RM - RRf) is the market (or equity) risk premium, BETA is the risk of the stock relative to the market, Rr is the real rate of interest, and CPI is the anticipated change in the consumer price index. With this information, we can rewrite equation (1) as follows: r = Rr + CPI + BETA(RM - RRf) (3) If we assume that the real rate of return remains approximately constant, we can view the required return as a function of the market risk premium and the anticipated change in the rate of inflation. We will set up these data tables in a separate spreadsheet, though that is not necessary. Data from the original spreadsheet is not critical for the what-if analysis. Secondly, including the what-if analysis in the original template would slow spreadsheet recalculation. Formulas that need to be entered appear at the end of the article. We will have a data input area at the top part of the spreadsheet and the data tables below. To create the data table, we simply put a row of numbers across the top of the range in which we want the values to appear, and a column of numbers along the left hand edge. These numbers will represent the inflation rate and the market risk premium. The upper left corner of the table (cell B22) is the formula we have given in equation (3). We will simply enter numbers for these border rows. You can do this using the / D(ata) F(ill) command. The command will prompt you for a range, a starting value -- remember that 1% is entered as .01 -- and then a step value. The step value is the amount by which the numbers are incremented as you go across or down each cell. To make the numbers compatible with historical data, choose a beginning inflation rate substantially below the historical average rate and go high enough to end with a value that would include possible future inflation rates. On average, the rate of inflation has been about 3.5% a year over the past 60 years, so the table illustrates a range from 1% through 12%. The market risk premium also goes from 1% to 12%. This then generates a range of values that is encompasses the past, but has enough scope to examine possible future changes. You can make the range of values as large as you like, but remember that 1-2-3 will calculate the required rate of return for each cell in the table. This can be a long process, even though the formula we are using is quite simple. Once you have the bordering cells filled in with the numbers you will be using, enter +$E$3+$E$4+$E$6*$E$8 in cell B22. To start creating the data table, type / D(ata) T(able). Since you want to create a two-way table, highlight 2, and hit the Enter or Return key. 1-2- 3 will prompt you for the table range, and you must highlight with the cursor the entire range of cells you want. This range must include both the bordering row and column, including the formula that is used in creating the values from cell B22. Having done this, 1-2-3 will prompt you for the first input cell. This cell is the variable that will be changed down the left-hand column. Since that is anticipated rate of inflation, highlight cell E4, which contains that value. Finally you are prompted for the second input cell. This will input the variable that is changed along the top row, which is the market risk premium, so highlight cell E6, where that number is located. As soon as you finish entering the relevant values 1-2-3 automatically calculates the results of the formula in every cell of the table. The value at the intersection of any market risk premium and any inflation rate is exactly the required rate of return for those values. For example, if inflation is expected to be 4% a year, and the market risk premium is expected to be 5%, an investor's required rate of return for a stock with a beta of 1.0 (as IBM has), is 11.5%. A B C D E F G H 19 Required Rate of Return 20 21 Market Risk Premium 22 13.50% 1% 2% 3% 4% 5% 6% 23 1% 4.50% 5.50% 6.50% 7.50% 8.50% 9.50% 24 2% 5.50% 6.50% 7.50% 8.50% 9.50% 10.50% 25 3% 6.50% 7.50% 8.50% 9.50% 10.50% 11.50% 26 4% 7.50% 8.50% 9.50% 10.50% 11.50% 12.50% 27 Anticipated 5% 8.50% 9.50% 10.50% 11.50% 12.50% 13.50% 28 Inflation 6% 9.50% 10.50% 11.50% 12.50% 13.50% 14.50% 29 7% 10.50% 11.50% 12.50% 13.50% 14.50% 15.50% 30 8% 11.50% 12.50% 13.50% 14.50% 15.50% 16.50% 31 9% 12.50% 13.50% 14.50% 15.50% 16.50% 17.50% 32 10% 13.50% 14.50% 15.50% 16.50% 17.50% 18.50% 33 11% 14.50% 15.50% 16.50% 17.50% 18.50% 19.50% 34 12% 15.50% 16.50% 17.50% 18.50% 19.50% 20.50% Growth Rates, the Earnings Retention Ratio and P/E Ratios The next step of our analysis is to explore the relationship between the required rate of return, the expected rate of earnings growth and the P/E ratio. To do so, we must recall the basic dividend valuation model: DPS1 P0 = --------- (4) r - g The dividends estimated for any year can be determined by taking estimated earnings for that year and multiplying them by the payout ratio (1-b). This transforms equation (4) into: EPS1(1-b) P0 = -------------- (5) r - g As a consequence then, we can divide both sides of this equation by EPS1 (we will use E1 to shorten things) to get a "normal" price-earnings ratio. Po (1-b) ---- = --------- (6) E1 r - g This kind of price-earnings ratio is what we have called a normal or expected price-earnings ratio. That is, it relates today's value to projected earnings. As you can see, this P/E is affected by the payout ratio (and so by its converse the retention ratio), the investor's required rate of return and the expected rate of growth. We will assume that the average payout ratio does not change significantly over time. Since we are not going to change the payout ratio, the P/Es will be affected by changes in the required rate of return and the rate of growth. We will set up a data table immediately below the first one to examine how changes in those variables affect P/E estimates. The table below shows the results of that analysis. A B C D E F G H 37 Price - Earnings Ratios 38 39 Expected Rate of Growth 40 10.64 1% 2% 3% 4% 5% 41 1% ERR -50.00 -25.00 -16.67 -12.50 42 2% 50.00 ERR -50.00 -25.00 -16.67 43 3% 25.00 50.00 ERR -50.00 -25.00 44 4% 16.67 25.00 50.00 ERR -50.00 45 5% 12.50 16.67 25.00 50.00 ERR 46 6% 10.00 12.50 16.67 25.00 50.00 47 7% 8.33 10.00 12.50 16.67 25.00 48 8% 7.14 8.33 10.00 12.50 16.67 49 9% 6.25 7.14 8.33 10.00 12.50 50 Required Rate 10% 5.56 6.25 7.14 8.33 10.00 51 Of Return 11% 5.00 5.56 6.25 7.14 8.33 52 12% 4.55 5.00 5.56 6.25 7.14 53 13% 4.17 4.55 5.00 5.56 6.25 54 14% 3.85 4.17 4.55 5.00 5.56 55 15% 3.57 3.85 4.17 4.55 5.00 56 16% 3.33 3.57 3.85 4.17 4.55 57 17% 3.12 3.33 3.57 3.85 4.17 58 18% 2.94 3.13 3.33 3.57 3.85 59 19% 2.78 2.94 3.13 3.33 3.57 60 20% 2.63 2.78 2.94 3.13 3.33 61 21% 2.50 2.63 2.78 2.94 3.13 As you can see from the Table and from equation (5), an increase in the rate of growth will increase the price-earnings ratio, while increasing the required rate of return decreases the price-earnings ratio. For example, with a required rate of return of 10%, if IBM's rate of growth increases from 4% to 5%, then the company's price-earnings ratio increases from 8.33 to 10. If the required rate of return increases from 10% to 11%, with the 5% rate of growth, then the P/E decreases from 10 to 8.33. Finally, when you examine the numbers in the table, you will see that there are a few strange occurrences. First, running along a diagonal path there is the message ERR. If you check the row and column headings corresponding to these cells, you will see that the required rate of return and the rate of growth are the same. That simply makes the denominator of the P/E equation (6) zero, and dividing by zero is impossible, even for 1-2-3. Also all numbers above this diagonal are negative. 1-2-3 simply calculates all values whether they make sense or not. Of course the negative P/Es result from the required rate of return being less than the growth rate, and the model simply does not work under those conditions. One of the important factors that you must remember here is the relation between growth and the percentage of earnings the company retains for reinvestment (the retention ratio). We defined sustainable growth, gsus = ROE x b; that is, it measures a company's ability to grow without affecting its financing patterns. Thus the risk to the stockholders depends on the proportion of earnings it retains and the return that those reinvested earnings generate. Of course, we started with the assumption that the growth rate was not affected by the dividend payout ratio. Clearly this is not the case -- especially for large swings in the payout ratio. However the table (shown partially in Table 1) does allows us to make some extrapolation from growth rates and required returns to P/Es. At this point, you might well use the table in the reverse fashion. That is, if you determine what the company's current P/E is, you can determine the rate of return and growth rate implicit in that P/E. If these numbers appear out of line, it suggests that the current value is also out of line. For example, IBM is currently selling at a P/E of 13. This is consistent with a required return of 8% and an expected growth rate of 4% (which gives a price-earnings ratio of 12.5). Any difference between the required return and the growth rate of 4% will give the same P/E. An 8% required return is probably too low, since we have already seen in the March article that IBM's beta of 1.0 is consistent with a required return on 10%. This means that a growth rate of 6% would give the 12.5 P/E which is in line with our estimates of growth. We will examine the effects on valuation later. Before doing so, we will continue our program and go from the P/E data table to construct a table that will show how P/Es affect value. P/Es and Valuation Immediately below the data price-earnings table, we will construct another to determine values. Once again we need a two-way table, since we have two independent variables -- the price-earnings ratio and projected earnings. You can use the data fill command to create a column of P/Es and a row of earnings estimates. We have created a range of earnings estimates that is consistent with the range of values we determined from our MODELS spreadsheet. In addition, the price-earnings ratios we use cover the range of values we found before, going from 10 to 21, in 0.5 point increments. The valuation results are presented in table below. As you can see, it is quite easy to generate a wide range of possible values. Even with the limited part of the data table that is presented in Table 3 we have value estimates ranging from $63.00 to $189.00 a share. It probably comes as no surprise, though it may be a disappointment, to discover that IBM's current price is somewhere right in the middle of this range. Indeed based on current earnings, the company's P/E is 13 -- again right about in the middle of the range we used. A B C D E F G 63 Estimated Value 64 65 Projected Earnings 66 $115.00 $7.00 $7.50 $8.00 $8.50 67 9.0 $63.00 $67.50 $72.00 $76.50 68 9.5 $66.50 $71.25 $76.00 $80.75 69 10.0 $70.00 $75.00 $80.00 $85.00 70 10.5 $73.50 $78.75 $84.00 $89.25 71 11.0 $77.00 $82.50 $88.00 $93.50 72 11.5 $80.50 $86.25 $92.00 $97.75 73 12.0 $84.00 $90.00 $96.00 $102.00 74 12.5 $87.50 $93.75 $100.00 $106.25 75 13.0 $91.00 $97.50 $104.00 $110.50 76 13.5 $94.50 $101.25 $108.00 $114.75 77 14.0 $98.00 $105.00 $112.00 $119.00 78 P/E 14.5 $101.50 $108.75 $116.00 $123.25 79 Ratios 15.0 $105.00 $112.50 $120.00 $127.50 80 15.5 $108.50 $116.25 $124.00 $131.75 81 16.0 $112.00 $120.00 $128.00 $136.00 82 16.5 $115.50 $123.75 $132.00 $140.25 83 17.0 $119.00 $127.50 $136.00 $144.50 84 17.5 $122.50 $131.25 $140.00 $148.75 85 18.0 $126.00 $135.00 $144.00 $153.00 86 18.5 $129.50 $138.75 $148.00 $157.25 87 19.0 $133.00 $142.50 $152.00 $161.50 88 19.5 $136.50 $146.25 $156.00 $165.75 89 20.0 $140.00 $150.00 $160.00 $170.00 90 20.5 $143.50 $153.75 $164.00 $174.25 91 21.0 $147.00 $157.50 $168.00 $178.50 A Resolution (or at least an End) To recap our discussions, it would seem appropriate for a company such as IBM with a beta of 1.0 to offer a return in line with the market. Our long run history of market returns suggests about a 10% required return, although we could justify a rate as high as 13% if inflation takes a significant jump. Our historical estimates of growth ranged from about 5.5% to about 12% These data suggest a range of P/Es from about 8 to about 50, with growth rates of 10% or above producing irrational valuations. The slower rates of growth seem to be more likely in the current economic environment and are consistent with the lower rates of realized earnings and dividends growth. Under these conditions, a range of price-earnings ratios from 9 to about 15 seems reasonable. We would use a value at the upper end of this range if we thought IBM's required return would be closer to 10% than the 13% we estimated in the models template. This would also be the case if we thought the company was in a position to significantly increase its rate of growth. Such an increase does not seem likely in view of the current market for computers. Finally our earnings estimates ranged from $8.29 to $12.94. Values at the lower end of this range would appear more likely given the current market environment. If we were to take an estimate of $9.00 a share, and a P/E of 13, we get a value of $117.00 (Table 3). Unfortunately for the stock pickers among us, this is right on the current market price. Is IBM correctly valued by the market? We have established a set of conditions that appears reasonable, and those conditions tell us the market is about right. Does this mean you should buy IBM or should you avoid it? That is a question you must answer. Your answer will depend on your beliefs about IBM's growth and market conditions. The model only tells you what the value is given certain assumptions. You still have to decide which assumptions are in line with your beliefs. (c) Copyright 1988 by the American Association of Individual Investors